-- 书本例题

-- 例4.1
-- 向数据库jxgl的学生表s(sno,sname,sex,birthdate,clloege)中插入下的记录,用@@error进行错误检查。其中sno为主键,sname要求非空。
use jxgl
insert into s
	(sno, sname)
values('s3', '许文秀')
insert into s
	(sno, sname)
values('s3', '许文秀')
if @@error=2627
print '插入了主键相同的值,违反了实体完整性！'


-- 例4.2
-- 可以用select命令显示全局变量的值,如显示当前sql server版本类型。
select @@version


-- 例4.3
-- 声明一个长度为10个字符串的变量id并赋值。
declare @id nchar(10)
select @id='10010001'


-- 例4.4
-- 用print显示变量并生成字符串。sql语句如下：
-- (1)print
declare @x char(10)
set @x='loving'
print @x
print '最喜欢的歌曲是：'+@x
-- (2)select
declare @num int
set @num=128
select @num as '局部变量', @@servername as '全局变量'


-- 例4.5
-- 使用比较运算符计算表达式的值。
declare @exp1 int,@exp2 int
set @exp1=30
set @exp2=50
if @exp1<@exp2
print @exp1


-- 例4.6
-- 系统函数应用举例。
-- (1)定义变量字符串st,赋值“数据库原理及应用课程”,取子串“原理及应用”的操作如下：
declare @st varchar(50)
set @st='数据库原理及应用课程'
select substring(@st,4,5) as '运行结果'
-- (2)显示当前日期的运行结果。
select '当前日期'+convert(varchar(8),getdate(),7) as '运行结果'
-- (3)用print语句显示系统的当前系统时间。
print(convert(varchar(30),getdate()))+'.'


-- 例4.7
-- 在begin……end语句块中完成把两个变量的值交换。
declare @z int,@y int,@t int
set @z=12
set @y=24
begin
	set @t=@z
	set @z=@y
	set @y=@t
end
print '@z='+str(@z,2,0)+'	@y='+str(@y,2,0)


-- 例4.8
-- 在教学管理数据库中,如果c4号课程的平均成绩高于80分,则显示“c4号课程的平均成绩还不错”,否则显示“c4号课程的平均成绩一般”。
use jxgl
if (select avg(grade)
from sc
where cno='c4')>80
		print'c4号课程的平均成绩还不错'
else
		print'c4号课程的平均成绩一般'


-- 例4.9
-- 在教学管理数据库中,查询s表中学生所在系的中文名称。如“李芸”的学院是“信息学院”。
select sname as '姓名',
	case college
	when 'cs' then '计算机学院'
	when 'is' then '信息学院'
	when 'mc' then '机械学院'
	when 'ma' then '数学学院'
end as '学院'
from s
where sname='李芸'


-- 例4.10
-- 在教学管理数据库中,显示学生c4课程的“成绩等级”。
select sname as '姓名',
	case 
	when sc.grade>=90 then '优秀'
	when grade>=80 then '良好'
	when grade>=70 then '中等'
	when grade>=60 then '及格' 
	when grade<60 then '不及格'
end as '成绩等级'
from s join sc on s.sno=sc.sno and cno='c4'


-- 例4.11
-- 打印乘法口诀表。
declare @i int,@j int
declare @ss varchar(200)
set @i=1
while @i<=9
	begin
	set @j=1
	set @ss=''
	while @j<=@i
			begin
		set @ss=@ss+str(@j,1)+'*'+str(@i,1)+'='+str(@j*@i,2)+' '
		set @j=@j+1
	end
	print @ss
	set @i=@i+1
end


-- 例4.12
-- 判断表s中是否存在学号为s12的记录,如果存在就删除它,否则提示“查无此人”,最后插入姓名为“王萍”的记录。
if exists (select *
from s
where sno='s12')
	delete from s where sno='s12'
else 
	print '查无此人'
insert into s
values('s12', '王萍', '女', '1996-02-12', 'is')


-- 例4.13
-- 将零做除数进行测试。
begin try
	select 1/0 as '错误语句'
end try
begin catch
	select error_number() as '错误号', error_severity() as '严重性',
	error_state() as '状态号', error_procedure() as '名称',
	error_line() as '行号', error_message() as '错误信息'
end catch


-- 例4.14
-- 查询page_id为10的服务配置页有关信息。
select *
from sys.dm_os_buffer_descriptors
where page_id=10


-- 例4.15
-- 查看数据库jxgl的数据表s的存储结构。
-- dbcc ind ( <数据库名> | <数据库id> , <对象名>|<对象id>,<非聚集索引id> | 1 | 0 | -1 | -2 )
dbcc ind('jxgl','s',-1)
-- dbcc page (<数据库名> | <数据库id> , <pagefid>,<pagepid>,[<输出选项>])
dbcc traceon(3604)
dbcc page('jxgl',1,259,1)
go


-- 例5.1
-- 创建教学管理数据库jxgl。主数据库文件逻辑名为jxgl.mdf,保存路径为d;\jxglsys\data,日志文件的逻辑名为jxgl_log.ldf,保存路径为d:\jxglsys\data_log。主数据文件大小为3mb,文件大小不受限制,增长量为1mb；日志文件的初始大小为1mb,最大为20mb,整长比例为10%。
create database jxgl
	on
	(name=jxgl,
	 filename='c:\program files\microsoft sql server\mssql15.mssqlserver\mssql\data\jxgl2.mdf',
	 size=5,
	 filegrowth=1
	)
log on
	(name=jxgl_log,
	 filename='c:\program files\microsoft sql server\mssql15.mssqlserver\mssql\data\jxgl_log2.ldf',
	 size=2,
	 maxsize=20,
	 filegrowth=10%
	)
go


-- 例5.2
-- 为教学管理数据库jxgl增加容量,原来的数据库文件jxgl.mdf的初始分配空间为5mb(默认值),现将增至到10mb。
use jxgl
alter database jxgl
modify file
(name=jxgl
,size=10)
go


-- 例5.3
-- 为数据库jxgl增加辅助数据文件jxgl_1.ndf,初始大小为5mb,最大长度为30mb,按照5%增长。
use jxgl
alter database jxgl
add file
(name=jxgl_1,
filename='c:\program files\microsoft sql server\mssql15.mssqlserver\mssql\data\jxgl_2.ndf',
size=5,
maxsize=30,
filegrowth=5%
)
go


-- 例5.4
-- 删除数据库jxgl中的辅助数据文件jxgl_1.ndf.
use jxgl
alter database jxgl
remove file jxgl_2
go


-- 例5.5
-- 将已存在的数据库jxgl改名为gx_jxgl.
exec sp_renamedb'jxgl','gx_jxgl'
--drop database<数据库名>
go


-- 例5.6
-- 删除更名后的数据库gx_jxgl.
drop database gx_jxgl
go


-- 例5.8
-- 创建例5.7中数据库jxgl的表s、表c、表sc。
use jxgl
create table s
(
	sno nchar(9) not null
		constraint pk_sno primary key clustered
		check (sno like '201705121[0-9][0-9]'),
	sname nchar(8) not null,
	sex nchar(2) null,
	birthdate date null,
	college nchar(20) null
)
use jxgl
create table c
(
	cno nchar(4)not null,
	cname nchar(20)not null,
	description text null,
	credit real null,
	c_college nchar(20)not null,
	primary key(cno)
)
use jxgl
create table sc
(
	sno nchar(9) not null,
	cno nchar(4) not null,
	grade real null,
	primary key(sno,cno),
	foreign key (sno) references s(sno),
	foreign key(cno)references c(cno)
)
go


-- 例5.9
-- 在学生表s中,将sex的原数据长度2改为1.
use jxgl
alter table s 
alter column sex char(1) null
go


-- 例5.10
-- 在学生表中,将birthdate列名改为age,数据类型为smallint。
use jxgl
alter table s
drop column birthdate
alter table s
add age smallint
go


-- 例5.11
-- 在学生表s中删除列sno主键。
use jxgl
alter table s
drop constraint pk_sno
go


-- 例5.12
-- 删除数据库jxgl内的学生表s。
use jxgl
drop table s
go


-- 例5.13
-- 在教学管理数据库jxgl中,向学生表s中插入记录('s13','程晓晴','女','1996-10-11','cs')
use jxgl
insert into s
	(sno,sname,sex,birthdate,college)
values('s13', '程晓晴', '女', '1996-10-11', 'cs')
go


-- 例5.14
-- 在教学管理数据库jxgl中,把学生表s中学号为s2的学生姓名改为“王中桥”、出生日期改为1997-06-06.
use jxgl
update s
set sname='王中桥',birthdate='1997-06-06'
where sno='s2'
go


-- 例5.15
-- 在教学管理数据库jxgl中,删除学生表s中姓名为“张丽”的学生记录。
use jxgl
delete s
where sname='张丽'
go


-- 例5.16
-- 使用truncate table清空教学管理数据库jxgl的表c。
truncate table c
go


-- 例6.1
-- 查询全体学生的学号与姓名
use jxgl
select sno, sname
from s


-- 例6.2
-- 查询全体学生的姓名、学号、所在学院
use jxgl
select sname, sno, college
from s


-- 例6.3
-- 查询全体学生的详细记录
use jxgl
select *
from s
-- 等价于：
use jxgl
select sno, sname, sex, age, college
from s


-- 例6.4
-- 查询全体学生的姓名及年龄
use jxgl
select sname, year(getdate())-year(birthdate)
from s


-- 例6.5
-- 查询全体学生的姓名、年龄和所在学院，要求用小写字母表示所有系名
use jxgl
select sname, year(getdate())-year(birthdate), lower(college)
from s

-- 用户通过指定别名来改变查询结果的列标题
use jxgl
select sname as '姓名', year(getdate())-year(birthdate) as '年龄', lower(college) as '所在学院'
from s


-- 例6.6
-- 查询选修了课程的学生学号
use jxgl
select sno
from sc

-- 去掉结果表中的重复行(使用distinct关键字)
use jxgl
select distinct sno
from sc


-- 例6.7
-- 查询计算机学院(cs)全体学生名单
use jxgl
select sname
from s
where college = 'cs'


-- 例6.8
-- 查询所有年龄在20周岁以下的学生姓名及其年龄
use jxgl
select sname as '姓名', year(getdate())-year(birthdate) as '年龄'
from s
where year(getdate())-year(birthdate) < 20


-- 例6.9
-- 查询考试成绩有不合格的学生的学号
use jxgl
select distinct sno
from sc
where grade < 60


-- 例6.10
-- 查询在1996年7月1日至1998年6月30日(包括1996年7月1日和1998年6月30日)之间的学生的姓名、所在学院和出生日期
use jxgl
select sname, college, birthdate
from s
where birthdate between '1996-07-01' and '1998-06-30'


-- 例6.11
-- 查询年龄不在20~23岁之间的学生姓名、所在学院和年龄
use jxgl
select sname, college, year(getdate())-year(birthdate) as 'age'
from s
where year(getdate())-year(birthdate) not between 20 and 23


-- 例6.12
-- 查询计算机学院(cs)、数学学院(ma)和信息学院(is)学生的姓名和性别
use jxgl
select sname, sex
from s
where college in('cs','ma','is')

-- 写成多个or运算符结合的模式
use jxgl
select sname, sex
from s
where college = 'cs' or college = 'ma' or college = 'is'


-- 例6.13
-- 查询学号为s3的学生的详细情况
use jxgl
select *
from s
where sno like 's3'

-- 也可以写作：
use jxgl
select *
from s
where sno='s3'


-- 例6.14
-- 查询所有姓“李”的学生的姓名、学号和性别
use jxgl
select sname.sno, sex
from s
where sname like '李%'


-- 例6.15
-- 查询缺少成绩的学生的学号和相应的课程号
use jxgl
select sno, cno
from sc
where grade is null


-- 例6.16
-- 查询所有有成绩的学生学号和课程号
use jxgl
select sno, cno
from sc
where grade is not null


-- 例6.17
-- 查询计算机学院(cs)在1997年9月1日前出生的学生姓名和出生日期
use jxgl
select sname, birthdate
from s
where college = 'cs' and birthdate < '1997-09-01'


-- 例6.18
-- 查询选修了课程号为c3课程的学生的学号以及成绩,查询结果按分数的降序排列
use jxgl
select sno, grade
from sc
where cno = 'c3'
order by grade desc


-- 例6.19
-- 查询全体学生情况,查询结果按所在学院的学院名升序排列,同一学院中的学生按照出生日期降序排列
use jxgl
select *
from s
order by college,birthdate desc


-- 例6.20
-- 查询表s中的男女学生人数
use jxgl
select sex as '性别', count(*) as '人数'
from s
group by sex


-- 例6.21
-- 查询选修每门课程的课程号及参加该门课程考试的学生总人数
use jxgl
select cno as '课程号', count(*) as '人数'
from sc
where grade is not null
group by cno


-- 例6.22
-- 查询选课人数超过8人的课程号
use jxgl
select cno as '课程号', count as '人数'
from sc
group by cno
having count(sno) >= 8


-- 例6.23
-- 查询选修了4门以上课程的学生学号
use jxgl
select sno
from sc
group by sno
having count(*) > 4


-- 例6.24
-- 从sc表中输出学习c1号课程的成绩前3名的学生的学号和总分
use jxgl
select top 3
	sno, grade
from sc
where cno = 'c1'
order by grade desc


-- 例6.25
-- 在sc表中查询总分排在前面20%的学生的学号和总分
use jxgl
select top 20 percent
	sno as '学号', sum(grade) as '总分'
from sc
group by sno
order by sum(grade) desc


-- 例6.26
-- 将表sc所有成绩不及格学生的学号都存入grade_npass表中
use jxgl
select distinct(sno)
into grade_npass
from sc
where grade < 60


-- 例6.27
-- 查询选修了c2号课程或选秀了c4课程的学生的学号
use jxgl
	select sno
	from sc
	where cno = 'c2'
union
	select sno
	from sc
	where cno = 'c4'


-- 例6.28
-- 查询及选秀了c1号课程或选修了c2号课程的学生的学号
use jxgl
	select sno
	from sc
	where cno = 'c1'
intersect
	select sno
	from sc
	where cno = 'c2'


-- 例6.29
-- 查询选修了c1号课程但没有选修c3号课程的学生的学号
use jxgl
	select sno
	from sc
	where cno = 'c1'
except
	select sno
	from sc
	where cno = 'c3'


-- 例6.30
-- 查询每个学生及其选修课程情况
use jxgl
select *
from s inner join sc on s.sno=sc.sno


-- 例6.31
-- 查询计算机学院(cs)的学生所选课程的课程号和平均成绩
use jxgl
select sc.cno, round(avg(sc.grade),1) as 'average'
from s join sc on s.sno = sc.sno and s.college = 'cs'
group by cno


-- 例6.32
-- 在sc表中查询选修c4课程的同学中成绩高于学号为s3同学成绩的所有学生原足球,并且按成绩降序排列
use jxgl
select a.sno, a.cno, a.grade
from sc a inner join sc b
	on a.cno = 'c4' and a.grade > b.grade and b.sno = 's3' and b.cno = 'c4'
order by grade desc


-- 例6.33
-- 查询90分以上学生的学号、姓名、选修课程号、选修课程名和成绩
use jxgl
select s.sno, s.sname, s.cno, c.cname, sc.grade
from s join sc
	on s.sno = sc.sno and grade >= 90
	join c on sc.cno = c.cno


-- 例6.34
-- 查询每个学生及其选修课程的成绩情况(含为选课程的学生信息)
use jxgl
select s.*, cno, grade
from s left join sc on s.sno = sc.sno


-- 例6.35
-- 查询所有学生可能的选课情况
use jxgl
select s.*, sc.cno, grade
from s cross join sc


-- 例6.36
-- 查询与许文秀在同一个学院学习的学生学号、姓名和所在学院。
-- step1
use jxgl
select college
from s
where sname='许文秀'
-- step2
use jxgl
select sno, sname, college
from s
where college='cs'
-- final
use jxgl
select sno, sname, college
from s
where college in
		(select college
from s
where sname = '许文秀')


-- 例6.37
-- 查询选修了c3号课程的学生的姓名和所在学院
use jxgl
select sname, college
from s
where sno in 
		(select sno
from sc
where cno = 'c3')


-- 例6.38
-- 查询成绩在80分以上的学生的学号、姓名及所在学院
use jxgl
select sno, sname, college
from s
where sno = any (select sno
from sc
where grade > 80)


-- 例6.39
-- 查询其他学院比计算机学院(cs)某哦写生年龄小的学生姓名和年龄
use jxgl
select sname, year(getdate())-year(birthdate)
from s
where birthdate > some(select birthdate
from s
where college = 'cs'
	and college <> 'cs'


-- 例6.40
-- 查询每一位学生比自己平均成绩高的所有成绩,并输出学生的学号、课程号和成绩
use jxgl
select sno, cno, grade
from sc as a
where grade >= 
		(select avg(grade)
from sc as b
where a.sno = b.sno)


-- 例6.41
-- 查询所有选修了c2课程的姓名
use jxgl
select sname
from s
where exists
				(select *
from sc
where sno=s.sno and sc.cno = 'c2')


-- 例6.42
-- 查询计算机学院没有选秀c4课程的学生学号、姓名
use jxgl
select sno, sname
from s
where not exists (select *
from sc
where sno = s.sno and sc.cno = 'c4'
	and college = 'cs')


-- 例6.43
-- 对每一个学员,求学合适呢个的平均年龄,并把结果存入数据库
-- 创建一个新表coll_age
use jxgl
create table coll_age
(
	college nchar(20),
	avg_age real
)
-- 按题目要求分组求值并存入新表
use jxgl
insert into coll_age
	(college,avg_age)
select college, avg(year(getdate())-year(birthdate))
from s
group by college


-- 例6.44
-- 删除计算机学院(cs)所有学生的选课记录
use jxgl
delete from  sc
where 'cs' = (select college
from s
where s.sno = sc.sno)


-- 例6.45
-- 将计算机学院(cs)学生的c4课程成绩提高5%
use jxgl
update sc
set grade = grade + grade*0.05
where 'cs' = (select college
from s
where s.sno=sc.sno and cno = 'c4')


-- 例6.46
-- 声明一个名为s_cursor的游标,用于读取计算机学院(cs)的所有学生的信息
use jxgl
declare s_cursor cursor
for select *
from s
where college = 'cs'


-- 例6.47
-- 打开例6.46所声明的游标
open s_cursor


-- 例6.48
-- 从例6.47中打开的游标中读取数据
fetch next from s_cursor


-- 例6.49
-- 关闭例6.46所声明的游标
close s_cursor


-- 例6.50
-- 释放例6.46所声明的游标
deallocate s_cursor


-- 例6.51
-- 声明一个名为s_curf的游标,用于读取女同学的信息,并读取第3条记录
use jxgl
declare s_cuef scroll cursor 
for select sname, sex, birthdate
from s
where sex = '女'
open s_curf
fetch absolute 3 from s_curf
select @@cursor_rows


-- 例6.52
-- 在教学管理数据库jxgl中,利用循环的print语句输出s表中计算机学院(cs)的女同学信息
use jxgl
declare @info varchar(200)
declare @curs cursor
set @curs = cursor scroll dynamic
		for 
		select '学号是：' + sno + '；姓名是：' + sname + '；性别是：' + sex + '；年龄是：' + convert(varchar(3),year(getdate())-year(birthdate)) + '学院是：' + college
from s
where sex = '女' and college = 'cs'
open @curs
fetch next from @cues into @info
while (@@fetch_status = 0)
	begin
	print @info
	fetch next from @curs into @info
end


-- 例6.53
-- 声明一个游标s_cur,用于读取学生表s中男同学的信息,用cursor_status函数测试游标的存在状态
use jxgl
declare s_cur scroll cursor for 
select *
from s
where sex = '男'
open s_cur
select cursor_status('global','s_cur')
close s_cur
select cursor_status('global','s_cur')


-- 例6.54
-- 声明1一个游标s_cur用以读取学生表中的男同学的信息,并将第三个男同学的出生日期修改为1996-08-11
use jxgl
declare s_cur scroll cursor for 
		select *
from s
where sex = '男'
open s_cur
fetch absolute 3 from s_cur
update s
set birthdate = '1996-08-11'
where current of s_cur


-- 例7.2
-- 建立数学学院(ma)学生视图v_ma,并哟啊求进行修改和插入操作时仍需保证该视图只有数学学院的学生
use jxgl
go
create view v_ma
as
	select sno, sname, birthdate
	from s
	where college = 'ma'
	with check option


-- 例7.3
-- 创建学生选修课程的门数与平均成绩的视图c_g,其中包含的属性列为(sno,c_num,avg_grade)
use jxgl
go
create view c_g
(
	sno,
	c_num,
	avg_grade
)
as
	select sno, count(sno), round(avg(grade),2)
	from sc
	where grade is not null
	group by sno


-- 例7.4
-- 创建2000年1月1日前参加工作的女教师和职工的分区视图
use jxgl
create view t_e_info
as
			select e_no, e_name, e_sex, j_w_date, e_unit, job, title, e_cv
		from jxgl.dbo.t_info
		where j_w_date<='2000-01-01' and e_sex = '女'
	union all
		select e_no, e_name, e_sex, j_w_date, e_unit, job, title, e_cv
		from staff.dbo.e_info
		where j_w_date <= '2000-01-01' and e_sex = '女'


-- 例7.6
-- 修改例7.2中的视图v_ma,并要求该视图只查询数学学院(ma)的男学生
alter view v_ma
as
	select sno, snoame, birthdate
	from s
	where college = 'ma' and sex = '男'
with check option



-- 例7.7
-- 在例7.2建立的数学学院(ma)学生试图v_ma中,将学号为s13的学生的姓名改为’马常友’
use jxgl
update v_ma
set sname = '马常友'
where sno = 's13'

-- 转换为基本表的更新语句
use jxgl
update s
set sname = '马常友'
where sno = 's6' and college = 'ma'


-- 例7.8
-- 删除数学学院学生视图v_ma 中学号为s15的记录
use jxgl
delete
from v_ma
where sno = 's15'
-- 转换为对基本表的更新语句为
use jxgl
delete
from s
where sno = 's15' and college = 'ma'


-- 例7.10
-- 为例5.7教学管理数据库的课程表c的列cname创建名为i_cname的唯一索引
use jxgl
create unique index i_cname
on c(cname)


-- 例7.11
-- 为选修课程表sc的cno,grade列创建名为i_cno_grade的复合索引
use jxgl
create index i_cno_grade
on sc(cno asc,grade asc)


-- 例7.12
-- 为表c创建输入成批数据是忽略重复值的索引,索引名为i_cname_credit,填充因子取60
use jxgl
create unique nonclustered index i_cname_credit
on c(cname asc,credit asc)
with pad_index,
fillfactor = 60,
ignore_dup_key


-- 例7.13
-- 创建一个教师信息表,并定义了主键约束和唯一性约束
use jxgl
create table teacher
(
	tno char(6) primary key,
	tname char(8) unique,
	tsex char(2),
	birthday date,
	title char(12),
	salary real
)


-- 例7.14
-- 查看例5.7教学管理数据库c表的索引
use jxgl
exec sp_helpindex c


-- 例7.15
-- 将例7.10中的索引i_cname更名为i_c
use jxgl
exec sp_rename 'c.i_name','i_c'


-- 例7.16
-- 删除例7.15中的索引i_c
use jxgl
drop index c.i_c


-- 例7.17
-- 对教学管理数据库s表的所有索引重建,并设定填充因子为60
use jxgl
alter index all on s
rebuild with (fillfactor = 60,
sort_in_tempdb = on 
)


-- 例7.18
-- 以下语句开启数据库jxgl自动更新统计信息功能
alter database jxgl
set auto_update_statistics on


-- 例7.19
-- 使用以下语句开启数据库 
alter database jxgl
set auto_update_statistics_async on


-- 例8.1
-- 在教学管理数据库中,显示表s的相关性信息
use jxgl
exec sp_depends @objname = 's'


-- 例8.2
-- 查看d:\jxglsys目录
use master
exec xp_fixeddrives


-- 例8.3
-- 查看服务器上所有固定驱动器,以及每个驱动器的可用空间
use master
exec xp_fixeddrives


-- 例8.4
-- 在教学管理数据库中,利用“新建存储过程”面板,创建学号和课程数参数的成绩查询存储过程sc_grade
create procedure sc_grade
	-- add the parameters for the stored procedure here
	@par_sno char(9),
	@par_cno char(4)
as
begin
	-- set nocount on added to prevent extra result sets from
	-- interfering with select statements
	set nocount on;
	select grade
	from sc
	where sno=@par_sno and cno=@par_cno
end
go


-- 例8.5
-- 利用例5.7教学管理数据库的sc表,返回学号s3的学生的成绩情况
use jxgl
go
create procedure s3_grade
as
select *
from sc
where sno='s3'


-- 例8.6
-- 利用例5.7教学管理数据库的三个基本表,创建一个存储过程ps_grade,输出指定学生的姓名及课程名称和成绩信息。
use jxgl
go
create procedure ps_grade
	@s_name char(8)
as
select sname, cname, grade
from s join sc on s.sno=sc, sno
and sname=@s_name
	join c on sc.cno=c.cno


-- 例8.7
-- 先判断存储过程insert_s是否存在,如果不存在,则创建一个带参数的存储过程insert_s,用于向表s表添加一个新的元组.
use jxgl
go
if exists(select name
from sysobjects
where name='insert_s' and type='p')
	drop procedure insert_s
go
create procedure insert_s
	@s_no nchar(9),
	@s_name nchar(8),
	@s_sex nchar(2),
	@s_birthdate date,
	@s_college nchar(20)
as
insert into s
	(sno,sname,sex,birthdate,college)
values
	(@s_no, @s_name, @s_sex, @s_birthdate, @s_college)


-- 例8.8
-- 利用例5.6教学管理数据库的三个基本表,创建一个存储过程pv_grade,输入一个学生姓名,输出该学生所有选修课程的平均成绩。
use
go
create procedure pv_grade
	@s_name char(8)=null,
	@s_avg real output
as
select @s_avg=avg(grade)
from s join sc on s.sno=sc.sno and sname=@s_name
	join c on sc.cno=c.cno



-- 例8.9
-- 创建存储过程,在例5.7教学管理数据库的s表上声明并打开一个游标。
use jxgl
go
create procedure o_s_cur
	@s_cursor varting output
as
set @s_cursor=cursor for
	select *
from s
where birthdate<'1997-09-01'
open @s_cursor


-- 例8.10
-- 创建加密存储过程encrypt_m,然后利用sp_helptext查看其内容。
use jxgl
go
create procedure encrypt_m
with
	encryption
as
select *
from s
exec sp_helptext encrypt_m


-- 例8.11 
-- 创建一个输入学生姓名,输出该学生姓名、课程号和成绩的存储过程p_grade;再创建存储过程m_pv,该存储过程利用游标,每查询出一个男同学,就将姓名传给p_grade,输出相关信息
use jxgl
go
create procedure p_grade
	@s_name char(8)=null
as
declare @s_m nchar(8),@c_m nchar(4),@g_e real
select @s_m=sname, @c_m=cname, @g_e=grade
from s join sc on s.sno=sc.sno and sname=@s_name join c on sc.cno=c.cno
print @s_m+@c_m+str(@c_e)

create procedure m_pv
as
declare @s_name nchar(8)
declare m_cur scroll cursor for	select sname
select sname
from s
where sex='男'
open m_cur
fetch next from m_cur into @s_name
exec dbo.p_grade @s_name
while (@@fetch_status=0)
	begin
	fetch next from m_cur into @s_name
	exec dbo.p_grade @s_name
end
close m_cur


-- 例8.12
-- 调用例8.6定义存储过程ps_grade.
use jxgl
declare @name char(9)
set @name='许文秀'
exec ps_grade @name


-- 例8.13
-- 调用例8.7定义的存储过程insert_s,向s表插入元组('s14','陈淑霞','女','1996-12-20','mc')
use jxgl
exec insert_s 's14','陈淑霞','女','1996-12-20','mc'


-- 例8.14
declare @s_avg real
exec pv_grade '刘德峰',@s_avg output
print'刘德峰平均成绩为：'+str(@s_avg)
''


-- 例8.15
-- 调用例8.9定义存储过程。需要声明一个局部游标变量,执行上述存储过程,通过游标变量读取元组。
declare @my_cursor cursor
exec o_s_cur @s_cursor=@my_cursor output
fetch next from @my_cursor
while (@@fetch_status=0)
begin
	fetch next from @my_cursor
end
close @my_cursor
deallocate @my_cursor


-- 例8.16
-- 查看存储过程pv_grade的相关内容信息。
use jxgl
exec sp_helptext pv_grade


-- 例8.17
-- 查看存储过程pv_grade的名称、参数等相关内容信息。
use jxgl
exec sp_help m_pv


-- 例8.18
-- 在例8.6中,将存储过程修改为一个输入参数(学生姓名)和两个输出参数(总成绩和平均成绩)
use jxgl
go
alter procedure ps_grade
@s_name char(8),@s_avg real,output,@s_sum int output
as
select @s_avg=avg(grade), @s_sum=sum(grade)
from s join sc on s, sno
=sc.sno and sname=@s_name
	join c on sc.cno=c.cno


-- 例8.19
-- 调用例8.18中修改后的存储过程ps_grade.
use jxgl
declare @s_sum1 int,@s_avg1 real
exec ps_grade'刘世元',@s_sum1 output,@s_avg1 output
print'刘世元总分是：'+str(@s_sum1)+''
平均成绩是：'+str(@s_avg1)'


-- 例8.20
-- 删除存储过程sc_grade.
use jxgl
drop procedure sc_grade


-- 例8.21
-- 在教学管理数据库中,利用ssms图形化方式为学生表s创建一个简单的dml触发器s_i_u,在插入和修改数据时,都会自动显示提示信息。
-- 在图8.9中修改transact——sql语句如下：
create trigger s_i_u
on s
for
insert,update
as
	print'对s表进行了数据的插入或修改'
-- 单击‘执行’按钮,即可完成触发器的创建


-- 例8.22
-- 在教学管理数据库中,将表s中学号为s11的学生出生日期修改为1996-12-24.
use jxgl
update s
set sirthdate='1996-12-24'
where sno='s11'


-- 例8.23
-- 在教学管理数据库中,用transact.sql语句为sc表创建一个delete类型的触发器del_count,删除数据时,显示删除元组的个数。
use jxgl
go
create trigger del_count
on sc
	for delete
		as
		declare @count varchar(50)
		select @count='sc表中'+str(@@rowcount,3)+'个元组被删除'
		print @count
		return
	


-- 例8.24
-- 删除表sc中所有机械学院(mc)的学生元组。
use jxgl
delete
from sc
where sno in (select sno
from s11
where college='mc')


-- 例8.25
-- 在教学管理数据库中,如果删除表s中的学号为s7元组,因为涉及与sc表的外键联系,需要创建s表的instead of(不能是after|for)触发器,使得在删除s表元组前,必须先删除sc表中由外键的元组
use jxgl
go
create trigger s_del
	on s
		instead of delete
	as
		delete from sc where sc.sno in
				(select sno
from deleted)
		return
		


-- 例8.26
-- 假设课程表c与选课表sc没有外键关系,在c表中创建update 和delete触发器,当修改或删除c表中的cno的值时,同时修改或删除sc表中相应的cno。
create trigger c_update
on c after update,delete
as
	begin
	if(update(cno))
		upfate sc
	set cno
	=
	(select cno
	from inserted)
	where cno=
	(select cno
	from deleted)
	else
	delete from sc
		where cno in (select cno
	from deleted)
end
	


-- 例8.27
-- 创建jxgl数据库作用域ddl触发器,当删除或修改一个数据表时,提示禁止该操作,然后回滚删除表操作
create trigger ddl_table trigger
on datarase
for drop_table,alter_table
as
	print'对不起,不能对数据表进行删除或修改操作,请联系dba'
	rollback
-- 测试删除表
use
go
drop table sc


-- 例8.28
-- 如果当前服务器上发生任何create_database事件,ddl触发器将输出消息。
if exists (select *
from sys.server_triggers
where name = 'ddl_trig_database')
drop trigger ddl_trig_database
	on all server;
go
create trigger ddl_trig_database
on all server
	for create_database
	as
		print'对不起,不允许创建数据库'
		rollback
		--测试创建数据库
	create database test		


-- 例8.29
-- 利用sp_helptext查看例8.23创建的触发器del_count内容。
use jxgl
exec sp_helptext del_count


-- 例8.30
-- 修改例8.21中的触发器s_i_u,使得对学生表s进行插入和修改操作时,自动给出操作无效的提示信息,并撤销此次操作。
use jxgl
goalter trigger s_i_u
on s
instead ofinsert,update
as
	print'你执行的插入或修改操作无效！'


-- 例8.31
-- 在教学管理数据库中,删除s表上的触发器del_count.
use jxgl
go
drop trigger del_count


-- 例8.32
-- 在教学管理数据库中,禁用s表上创建的所有触发器。
use jxgl
go
alter table s11disable trigger all


-- 例8.33
-- 在教学管理数据库中,定义一个函数s_avg,当给定一个学生姓名,返回该学生的平均成绩。
use jxgl
go
create function s_avg
(@s_name nchar(8))
returns real
as
begin
	declare @s_average real
	select @s_average=avg(grade)
	from s join sc on s.sno and s.sname=@s_name
	return @s_average
end


-- 例8.34
-- 调用例8.28中定义的函数s_avg,求得学生“王丽萍”的平均成绩。
use jxgl
go
print dbo.s_avg('王丽萍')


-- 例8.35
-- 在教学管理系统中,定义函数s_cname,当给定一个学生的学号,返回该学生所学的所有课程名。
use jxgl
go
create function s_cname
(@s_no nchar(9))
returns table
as
	return(select sno, cname
from sc join c on sc.cno=c.cno and sno=@s_no)



-- 例8.36
-- 调用例8.35中定义的内联表值型函数s_cname,求得学号为s6的学生选修课的课程名。
use jxgl
go
select *
from s_cname('s6')


-- 例8.37
-- 在教学管理数据库中,定义一个函数s_table,输入一个学生的姓名,返回该姓名的学生的成绩表。
use jxgl
go
create function s_table
(@s_name nchar(8))
returns @tb table
(
	tb_sno nchar(9),
	tb_name nchar(8),
	tb_cno nchar(4),
	tb_gread real
)
as
begin
	insert into @tb
	select s.sno, sname, cno, grade
	from s join sc on s.sno=sc.sno
	return
end


-- 例8.38
-- 调用例8.32中定义的多语句表值型函数s_table,求得学生“李小刚”的成绩表
use jxgl
go
select *
from s_table('李小刚')


-- 例8.39
-- 删除用户定义函数s_table
use jxgl
go
drop function s_table